package com.xgame.util.jdbc;

import java.sql.*;
import java.util.*;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

/*
 * 对spring JdbcTemplate 进行封装
 */
public class JdbcTemplateWrapper extends JdbcTemplate {

	/**
	 * prepare使用name代替占位符使用
	 * 
	 * String sql = "select count(0) from USER where id = :id";
	 * NamedParameterJdbcTemplate template = new
	 * NamedParameterJdbcTemplate(this.getDataSource()); Map para = new
	 * ConcurrentHashMap("id", 1); updateByNamedParameter(sql, para);
	 * 
	 * @return
	 */
	public int updateByNamedParameter(String sql, Map paramMap) {
		return getNamedParameterJdbcTemplateByDataSource(getDataSource())
				.update(sql, paramMap);
	}

	/**
	 * prepare使用name代替占位符使用
	 * 
	 * String sql = "select count(0) from USER where id = :id";
	 * NamedParameterJdbcTemplate template = new
	 * NamedParameterJdbcTemplate(this.getDataSource()); SqlParameterSource
	 * namedParameters = new MapSqlParameterSource("id", 1);
	 * template.queryForInt(sql, namedParameters);
	 * 
	 * @param dataSource
	 *            数据源
	 * @return
	 */
	private NamedParameterJdbcTemplate getNamedParameterJdbcTemplateByDataSource(
			DataSource dataSource) {
		return new NamedParameterJdbcTemplate(dataSource);
	}

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return new NamedParameterJdbcTemplate(getDataSource());
	}
	
	/**
	 * 返回值:HashTable
	 * 
	 * String sql = "select id,name from USER";
	 * 
	 * usage  : queryForHashTable(sql);
	 * return : { "1", "admin", "2", "hehe"}
	 * @param dataSource
	 *            数据源
	 * @return
	 */
	public Hashtable<String, String> queryForHashTable(String sql, Object... args){
		Hashtable<String, String> hashtable = query(sql, args, new ResultSetExtractor<Hashtable<String, String>>(){

			@Override
			public Hashtable<String, String> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				Hashtable<String, String> table = new Hashtable<String, String>();
				while(rs.next()){
					String key = rs.getString(1);
					String value = rs.getString(2);
					if(rs.wasNull() || StringUtils.isBlank(key) || StringUtils.isBlank(value)){
						continue ;
					}
					table.put(key, value);
				}
				
				return table;
			}
			
		});
		return hashtable;
	}
	
	/**
	 * 返回值:HashTable
	 * 
	 * String sql = "select id,name from USER whereid name like  CONCAT('%', ?, '%')";
	 * 
	 * usage  : queryForHashTable(sql, "%22&");
	 * return : { "1", "admin", "2", "hehe"}
	 * @param dataSource
	 *            数据源
	 * @return
	 */
	public Hashtable<String, String> queryForHashTable(String sql){
		return queryForHashTable(sql, argsToArray());
	}

	
	/**
	 * 返回值:HashTable
	 * 
	 * String sql = "select id,name from USER";
	 * 
	 * usage  : queryForHashTable(sql);
	 * return : { "1", "admin", "2", "hehe"}
	 * @param dataSource
	 *            数据源
	 * @return
	 */
	public <K, V> LinkedHashMap<K, V> queryForLikedHashMap(String sql, Class<K> key, Class<V> value, Object... args){
		LinkedHashMap<K, V> linkedHashMap = query(sql, args, new ResultSetExtractor<LinkedHashMap<K, V>>() {

			@Override
			public LinkedHashMap<K, V> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				LinkedHashMap<K, V> map = new LinkedHashMap<K, V>();
				while(rs.next()){
					K key = (K) rs.getString(1);
					V value = (V) rs.getString(2);
					if(rs.wasNull() || null == key || null == value){
						continue ;
					}
					map.put(key, value);
				}
				return map;
			}
			
		});
		return linkedHashMap;
	}
	
	/**
	 * 返回值:HashTable
	 * 
	 * String sql = "select id,name from USER whereid name like  CONCAT('%', ?, '%')";
	 * 
	 * usage  : queryForHashTable(sql, "%22&");
	 * return : { "1", "admin", "2", "hehe"}
	 * @param dataSource
	 *            数据源
	 * @return
	 */
	public <K, V> LinkedHashMap<K, V> queryForLikHashMap(String sql, Class<K> key, Class<V> value){
		return queryForLikedHashMap(sql, key, value, argsToArray());
	}

	Object[] argsToArray(Object... args){
		return args;
	}
}
